package org.jeecg.chase.common.dataaccess.supper;

import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import org.jeecg.chase.common.dataaccess.data.PageRequest;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DataSourceUtils;

import javax.sql.DataSource;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author leikai
 * @version 1.0
 * @description: 工具类
 * @date 2021/07/01/0001 17:00
 */
public class DatabaseSupport {

    public static RowMapper rowMapper(Class elementType) {
        if (elementType == null || Map.class.isAssignableFrom(elementType)) {
            return new ColumnMapRowMapper();
        }
        return new BeanPropertyRowMapper(elementType);
    }

    /**
     * 获取数据库类型
     *
     * @param dataSource
     * @return java.lang.String
     * @author leikai
     * @date 2021/07/01/0001 17:37
     */
    public static String getDatabaseType(DataSource dataSource) {
        try {
            DatabaseMetaData databaseMetaData = DatabaseSupport.getDatabaseMetaData(dataSource);
            String databaseType = DatabaseSupport.getDatabaseType(databaseMetaData.getDatabaseProductName());
            return databaseType;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return "";
    }

    /**
     * 根据数据库产品名获得数据库类型
     *
     * @param databaseProductName
     * @return java.lang.String
     * @author leikai
     * @date 2021/07/01/0001 17:37
     */
    public static String getDatabaseType(String databaseProductName) {
        try {
            String databaseType = databaseProductName;
            //databaseType取值范围："Apache Derby","DB2","MySQL","Microsoft SQL Server","Oracle","PostgreSQL","Sybase"
            if (databaseType.equalsIgnoreCase("MySQL")) {
                return "mysql";
            }
            if (databaseType.equalsIgnoreCase("Oracle")) {
                return "oracle";
            }
            if (databaseType.equalsIgnoreCase("PostgreSQL")) {
                return "postgresql";
            }
            if (databaseType.equalsIgnoreCase("Microsoft SQL Server")) {
                return "sqlserver";
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

    /**
     * 获得数据库元数据
     *
     * @param dataSource
     * @return java.sql.DatabaseMetaData
     * @author leikai
     * @date 2021/07/01/0001 17:37
     */
    public static DatabaseMetaData getDatabaseMetaData(DataSource dataSource) {
        Connection connection = DataSourceUtils.getConnection(dataSource);
        try {
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            return databaseMetaData;
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据数据库类型获得数据库分页语句
     *
     * @param pageRequest
     * @param sql
     * @param databaseType
     * @return java.lang.String
     * @author leikai
     * @date 2021/07/01/0001 17:37
     */
    public static String getPageSql(final PageRequest pageRequest, String sql, String databaseType) {
        int start = (pageRequest.getPageNo() - 1) * pageRequest.getPageSize();
        int end = start + pageRequest.getPageSize();
        if ("mysql".equals(databaseType)) {
            return sql + " limit " + start + "," + pageRequest.getPageSize();
        }
        if ("oracle".equals(databaseType)) {
            String sqlStr = null;
            try {
                CCJSqlParserManager parserManager = new CCJSqlParserManager();
                Select select = (Select) parserManager.parse(new StringReader(sql));
                PlainSelect plain = (PlainSelect) select.getSelectBody();
                List<OrderByElement> OrderByElements = plain.getOrderByElements();
                List<OrderByElement> newOrderByElements = new ArrayList<OrderByElement>();
                for (OrderByElement orderByElement : OrderByElements) {
                    String orderbyStr = orderByElement.toString();
                    OrderByElement elemnt = new OrderByElement();
                    Expression expression = new net.sf.jsqlparser.schema.Column(orderbyStr);
                    elemnt.setExpression(expression);
                    newOrderByElements.add(elemnt);
                }
                OrderByElement elemnt = new OrderByElement();
                Expression expression = new net.sf.jsqlparser.schema.Column("rownum");
                elemnt.setExpression(expression);
                newOrderByElements.add(elemnt);
                plain.setOrderByElements(newOrderByElements);
                sqlStr = plain.toString();
            } catch (Exception e) {
            }
            sqlStr = "select * from ( select row_.*, rownum rownum_ from ( " + sqlStr + ") row_ where rownum <=" + end + " ) where rownum_ > " + start;
            return sqlStr;
        }
        if ("postgresql".equals(databaseType)) {
            return sql + " limit " + pageRequest.getPageSize() + " offset " + start;
        }
        if ("sqlserver".equalsIgnoreCase(databaseType)) {
            return "select top " + end + sql.substring(sql.toLowerCase().indexOf("select") + 6);
        }
        return "";
    }

}
